SET NOCOUNT ON;
go
/*********************************************************************************************************
*Created/Source by:sfibich
*ScriptName:Get_AggregatedQueryStats													
*Version:1.0.2013.08.01
*SQL Versions:2008R2
*Purpose:This script gets a number of aggregated statistics for queries that share the same 
*query hash and query plan hash value.  The script also displays a query plan 
*associated with the query has/query plan hash row.
*					 
*********************************************************************************************************/
go
select
sq1.sql_statement 
,sq1.query_plan_hash 
,sq1.query_hash
,sq1.execution_count 
,sq1.avg_executions_per_second 
,sq1.creation_time 
,sq1.last_execution_time 
,convert(int,sq1.total_elapsed_time*.000001) as [total_elapsed_time_sec]
,sq1.total_logical_reads
,sq1.total_logical_writes
,sq1.total_physical_reads
,sq1.total_worker_time
,deqp.query_plan 
from
(
	SELECT 
	max(
	SUBSTRING(dest.text,statement_start_offset/2+1, 
		CASE WHEN statement_end_offset = -1  THEN (LEN(CONVERT(nvarchar(max),dest.text)) * 2) 
		ELSE statement_end_offset END - statement_start_offset / 2+1)  
	)AS sql_statement
	,deqs.query_plan_hash 
	,deqs.query_hash 
	,max(deqs.plan_handle) as [plan_handle]  /*This should make you think!*/
	,sum(execution_count) as [execution_count]
	,convert(numeric(38,3),convert(numeric(21,2),sum(execution_count))/case datediff(ss,min(deqs.creation_time),max(deqs.last_execution_time)) 
		when 0 then 1 
		else datediff(ss,min(deqs.creation_time),max(deqs.last_execution_time)) 
	end) as [avg_executions_per_second]
	,min(deqs.creation_time)as [creation_time]
	,max(deqs.last_execution_time) as [last_execution_time]
	,sum(deqs.total_elapsed_time) as [total_elapsed_time]
	,sum(deqs.total_logical_reads) as [total_logical_reads]
	,sum(deqs.total_logical_writes) as [total_logical_writes]
	,sum(deqs.total_physical_reads) as [total_physical_reads]
	,sum(deqs.total_worker_time) as [total_worker_time]
	FROM sys.dm_exec_query_stats AS deqs 
	cross apply
	sys.dm_exec_sql_text(sql_handle) AS dest 
	WHERE 
	dest.objectid is null 
	group by
	deqs.query_plan_hash 
	,deqs.query_hash 
) sq1
cross apply
sys.dm_exec_query_plan(sq1.plan_handle) as deqp
order by sq1.execution_count desc